﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace projekty2
{
    public partial class frmStatistiky : Form
    {
        public frmStatistiky()
        {
            InitializeComponent();
        }

        private void frmStatistiky_Load(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection(global::projekty2.Properties.Settings.Default.connectionKlesako);
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            SqlDataReader sqlDataReader;

            sqlCommand.CommandText = "SELECT count(*) FROM Projekty";
            sqlConnection.Open ();

            int returnValue = (int) sqlCommand.ExecuteScalar ();


            sqlCommand.CommandText = "SELECT count(*) FROM Projekty;";
            txtProjektyPocetProjektovSpolu.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Projekty where projektyStavId = '1';";
            txtProjektyMomentalneAnalyzovanych.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Projekty where projektyStavId = '2';";
            txtProjektyVProceseVyroby.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Projekty where projektyStavId = '3';";
            txtProjektyDokoncenych.Text = sqlCommand.ExecuteScalar().ToString();


            sqlCommand.CommandText = "SELECT count(*) FROM Ulohy";
            txtUlohyPocetEvidovanychUloh.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Ulohy where ulohyStatusId = '1';";
            txtUlohyZadane.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Ulohy where ulohyStatusId = '2';";
            txtUlohyOdovzdane.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Ulohy where ulohyStatusId = '3';";
            txtUlohyDokoncene.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Ulohy where ulohyStatusId = '4';";
            txtUlohyZrusene.Text = sqlCommand.ExecuteScalar().ToString();


            sqlCommand.CommandText = "SELECT count(*) FROM Prace;";
            txtPraceVSystemeSpolu.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Prace where pracaStatusId = '1';";
            txtPraceSchvalenych.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Prace where pracaStatusId = '2';";
            txtPraceNeschvalenych.Text = sqlCommand.ExecuteScalar().ToString();


            sqlCommand.CommandText =
                "WITH ZoradenePrace " +
                "AS( " +
                "	SELECT prace.popis as pracePopis, ROW_NUMBER() OVER (ORDER BY (pocetHodin * mzdaNaHod) DESC) AS 'RowNumber', pocetHodin * mzdaNaHod as 'suma' " +
                "	FROM prace, ulohy " +
                "	WHERE prace.ulohyId = ulohy.ulohyId " +
                ") " +
                "SELECT * FROM ZoradenePrace WHERE RowNumber = 1;";
            sqlDataReader = sqlCommand.ExecuteReader();
            if (sqlDataReader.Read ()) {
                txtNajnakladnejsiaPraca.Text = sqlDataReader["pracePopis"] + " ( " + sqlDataReader["suma"] + " Eur)";
            }
            sqlDataReader.Close();



            sqlCommand.CommandText =
                "WITH ZoradenePrace " +
                "AS( " +
                "	SELECT ulohaNazov, ROW_NUMBER() OVER (ORDER BY sum(pocetHodin * mzdaNaHod) DESC) AS 'RowNumber', sum(pocetHodin * mzdaNaHod) as 'suma' " +
                "	FROM prace, ulohy " +
                "	WHERE prace.ulohyId = ulohy.ulohyId " +
                "	GROUP BY ulohy.ulohyId, ulohy.ulohaNazov " +
                ") " +
                "SELECT * FROM ZoradenePrace WHERE RowNumber = 1;";
            sqlDataReader = sqlCommand.ExecuteReader();
            if (sqlDataReader.Read())
            {
                txtNajnakladnejsiaUloha.Text = sqlDataReader["ulohaNazov"] + " ( " + sqlDataReader["suma"] + " Eur)";
            }
            sqlDataReader.Close();



            sqlCommand.CommandText =
                "WITH ZoradenePrace " +
                "AS(  " +
                "	SELECT projekty.nazov as 'projektyNazov', ROW_NUMBER() OVER (ORDER BY sum(pocetHodin * mzdaNaHod) DESC) AS 'RowNumber', sum(pocetHodin * mzdaNaHod) as 'suma' " +
                "	FROM prace, ulohy, projekty " +
                "	WHERE prace.ulohyId = ulohy.ulohyId AND projekty.projektyId = ulohy.projektyId  " +
                "	GROUP BY projekty.projektyId, projekty.nazov " +
                ") " +
                "SELECT * FROM ZoradenePrace WHERE RowNumber = 1; ";
            sqlDataReader = sqlCommand.ExecuteReader();
            if (sqlDataReader.Read())
            {
                txtNajnakladnejsiProjekt.Text = sqlDataReader["projektyNazov"] + " ( " + sqlDataReader["suma"] + " Eur)";
            }
            sqlDataReader.Close();
            


            sqlCommand.CommandText = "SELECT count(*) FROM Uzivatelia;";
            txtPouzivateliaPocetUzivatelovVSysteme.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Uzivatelia where pozicieId = '4'";
            txtPouzivateliaManazerov.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Uzivatelia where pozicieId = '6'";
            txtPouzivateliaTesterov.Text = sqlCommand.ExecuteScalar().ToString();
            sqlCommand.CommandText = "SELECT count(*) FROM Uzivatelia where pozicieId = '5'";
            txtPouzivateliaProgramatorov.Text = sqlCommand.ExecuteScalar().ToString();




            sqlConnection.Close ();
        }
    }
}
